-- @owner: opentestcase033
-- @date: 2023-06-07
-- @testpoint: 利用create any trigger权限采用钓鱼方式提权sysadmin，合理报错

--step1:创建u_privilege_0003_1用户，切换到u_privilege_0003_1用户创建普通用户并赋create any trigger权限，预置表;expect:成功
create user u_privilege_0003_1 sysadmin password "test@123";
set role u_privilege_0003_1 password "test@123";
create user u_privilege_0003_2 with password "test@123";
grant create any trigger to u_privilege_0003_2;
create table t_privilege_0003(id int, name varchar(10));
insert into u_privilege_0003_1.t_privilege_0003 values(1, 'aaa');

--step2:切换到u_privilege_0003用户，创建函数、trigger并嵌入提权语句;expect:成功
set role u_privilege_0003_2 password "test@123";
create or replace function f_privilege_0003_1(integer) returns integer
   language sql
   security invoker as
   'alter user u_privilege_0003_2 sysadmin; select $1';
/

create or replace function f_privilege_0003_2() returns trigger
  security invoker as $e$
  begin
  perform f_privilege_0003_1(1000); return new;
  end $e$
language plpgsql;
/

create constraint trigger def
    after insert on u_privilege_0003_1.t_privilege_0003
    initially deferred
    for each row
    execute procedure f_privilege_0003_2();

--step3:高权限用户插入表数据促使触发器被调用;expect:失败
set role u_privilege_0003_1 password "test@123";
insert into u_privilege_0003_1.t_privilege_0003 values(1, 'aaa');

--step4:查看是否提权成功;expect:提权失败
select rolsystemadmin from pg_roles where rolname ='u_privilege_0003_2';

--step5:清理环境;expect:成功
reset role;
drop user u_privilege_0003_2 cascade;
drop user u_privilege_0003_1 cascade;
